if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RptCSCRThirdPartyBillingReport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[RptCSCRThirdPartyBillingReport]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[RptCSCRThirdPartyBillingReport]
(
	@CSCRExecutedStartDate				datetime,
	@CSCRExecutedEndDate				 	datetime,
	@InstitutionSelected							int = 10,--'All'	
	@DomainUserId            INT
)
 AS

-- =======================================================================
-- Procedure Name: dbo.RptCSCRThirdPartyBillingReport
--
-- Author:      Arthur A. Bianchini III
--
-- Create date: 4/17/2008
--
-- Description: Source of Report, "CSCR Third Party Billing Report"
--                  In Insight DB Reporting Services
--
--Purpose
--
-- Parameters:  
--     @StartDate                           
--     @EndDate                           
--     @Institution       
--		Include the following 3 additional sub activity type values, 'Other - Company Initiated', 'Other - PI Initiated,'
--     and 'Other - Joinly Initiated'
-- 6/1/2009 aab - change code to codeid where pt.arr_name = 'PROP_TYPE' 
-- =======================================================================

BEGIN

DECLARE @RespInstitutionId int

IF @InstitutionSelected = 11--'All - excluding DFCI'
	SET @RespInstitutionID = 10--'All'
ELSE
	SET @RespInstitutionID = @InstitutionSelected


	CREATE TABLE #TempThirdPartyBilling

	(ProposalID											INT NOT NULL,
	InstCode												varchar(10) NULL, 
	 ProposalNo											varchar(30) NULL, 
	 PIName												varchar(100) NULL,  
	 RespInstitution										varchar(32) NULL, 
	 InstitutionID											INT NULL,
     Department											varchar(60) NULL,
	 Unit														varchar(60) NULL,
	 PrimarySponsor									varchar(150) NULL,
	 SecondarySponsor								varchar(150) NULL,
	 ThirdPartyBilling									varchar(60) NULL,
	 ProtocolIDNo										varchar(30) NULL,
	 DateCSCRExecuted								datetime NULL,
	 CSCRExecutedStartDate						datetime NULL,
	 CSCRExecutedEndDate						datetime NULL,
	 DomainUserID											int NULL,
	 InstitutionSelected									varchar(32) NULL
	)

INSERT INTO #TempThirdPartyBilling

SELECT DISTINCT
p.prop_no as ProposalID,
p.inst_code as InstCode,
p.inst_no as ProposalNo, 
PIName = ISNULL(r.lName,'') + ', ' + ISNULL(r.fName,'') 
		+ CASE WHEN r.mi is NULL THEN ''
			ELSE ' ' + r.mi
		END,  
ei.Description,
ei.InstitutionId,
dept.unit_name as Department,
propunit.unit_name as Unit,
s.spon_name as PrimarySponsor,
s2.spon_name as SecondaySponsor,
CASE u6.p_sin_33
	WHEN '1-None' THEN 'None'
	WHEN '2-Simple' THEN 'Simple'
	WHEN '3-Complex' THEN 'Complex'
	WHEN '4-NA' THEN 'NA'
END as ThirdPartyBilling, 
x.x_inst_no as ProtocolIDNo,
--c.code_desc as Status, 
ps.stat_date as DateCSCRExecuted,
@CSCRExecutedStartDate,
@CSCRExecutedEndDate,
@DomainUserID,
@InstitutionSelected
FROM proposal p
JOIN	PropPds pd
	ON	p.prop_no = pd.prop_no
	AND	p.inst_code = pd.inst_code
	AND pd.first_pd = 1
JOIN	Faculty r
	ON	pd.Unique_ID = r.Unique_ID
JOIN	Sponspas s
	ON	p.spon_code = s.spon_code
	AND	p.inst_code = s.inst_code
LEFT JOIN	Sponspas s2--secondary sponsor
	ON	p.orig_spon = s2.spon_code
	AND	p.inst_code = s2.inst_code
JOIN	prop_stat ps --aab 4/11/2008  select date change to CSCR - Executed
	on ps.prop_no = p.prop_no 
	and ps.inst_code = p.inst_code 
	and ps.prop_stat = (select codeid 
									from codetab 
									where arr_name = 'prop_stat' 
									and inst_code = 'partners'
									and code_desc = 'CSCR - Executed')
	AND	ps.stat_date = (SELECT MAX(ps1.stat_date)
									FROM	prop_stat ps1
									WHERE	ps.prop_no = ps1.prop_no
										AND	ps.inst_code = ps1.inst_code
										AND	ps.prop_stat = ps1.prop_stat
										AND	(ps.stat_date BETWEEN @CSCRExecutedStartDate AND DATEADD(day,1,@CSCRExecutedEndDate)))
JOIN codetab pt
	on pt.codeid = p.prop_type 
	AND	pt.inst_code = p.inst_code
	and pt.arr_name = 'PROP_TYPE' 
	and pt.code_desc = 'Master'
JOIN Unit un 
	ON		p.inst_code = un.inst_code
	AND		p.unit_code = un.unit_code
JOIN Unit as inst
          ON    inst.inst_code = un.inst_code
          AND  left(un.deptid,4) = inst.deptID
          AND  len(inst.deptID) = 4
		  AND inst.int_unit_code in ('1200','1400','1700','2200','1400','FH','NWH','DFCI','DFPCC')
JOIN		InfoEdInstitutions ei
	ON	ei.int_unit_code = inst.int_unit_code
LEFT JOIN Unit as dept
          ON    dept.inst_code = un.inst_code
          AND  left(un.deptid,7) = dept.deptID
          AND  len(dept.deptID) = 7
LEFT JOIN Unit as propunit
          ON    propunit.inst_code = un.inst_code
          AND  left(un.deptid,10) = propunit.deptID
          AND  len(propunit.deptID) = 10
JOIN prop_u u2--get activity type
	ON p.inst_code = u2.inst_code 
	AND p.prop_no = u2.prop_no
	AND	u2.p_sin_14 = 'Clinical Research (C)'
JOIN prop_u u3-- get subactivity type
	ON p.inst_code = u3.inst_code 
	AND p.prop_no = u3.prop_no
	AND	(u3.p_sin_15 in  ('Trial - Jointly Initiated (C)','Trial - Other (C)','Trial - Company Initiated (C)','Trial - PI Initiated (C)',
'Other - Company Initiated (C)','Other - PI Initiated (C)','Other - Jointly Initiated (C)')--added on 9/10/08 by aab
	OR	u3.p_sin_15 like 'Compassionate Use%'
	OR	u3.p_sin_15 like 'Disease Mechanisms%')
LEFT JOIN		CodeTab f2
	ON	p.prop_stat = f2.codeID
	AND	p.inst_code = f2.inst_code
	AND f2.arr_name = 'PROP_STAT'
 LEFT JOIN app_xref x 
	on p.inst_code = x.inst_code 
	and p.prop_no = x.prop_no 
	and app_type in ('LA','HS') -- for human and animal studies
JOIN		prop_u u6--3rd party billing
	ON	p.inst_code = u6.inst_code
	AND	p.prop_no = u6.prop_no
	AND	u6.p_sin_33 is not null and u6.p_sin_33 <> ''
JOIN INSIGHT_RPT_DB.dbo.fnGetSecurityAgreements (@DomainUserId) sec --updated on 1/28/09
	ON p.prop_no = sec.InfoEdPropNumber

WHERE len(p.inst_no) = 11
	AND		p.system = 'pt'
	AND		p.inst_code = 'partners'
ORDER BY p.inst_no --QA

IF @InstitutionSelected = 11--'All - excluding DFCI'
   BEGIN
	DELETE FROM #TempThirdPartyBilling
	WHERE RespInstitution IN ('DFCI', 'DFPCC')
   END

	SELECT DISTINCT
	 ProposalNo, 
	 PIName,  
	 RespInstitution, 
	 Institutionid,
	 Department,
	 Unit,
	 PrimarySponsor,
	 SecondarySponsor,
	 ThirdPartyBilling,
	 ProtocolIDNo,
	 DateCSCRExecuted,
	 CSCRExecutedStartDate,
	 CSCRExecutedEndDate,	
	 CASE InstitutionSelected	
	WHEN	1	THEN	'BWH'
	WHEN	2	THEN	'DFCI'
	WHEN	3	THEN	'DFPCC'
	WHEN	4	THEN	'FH'
	WHEN	5	THEN	'IHP'
	WHEN	6	THEN	'MCL'
	WHEN	7	THEN	'MGH'
	WHEN	8	THEN	'NWH'
	WHEN	9	THEN	'SRH'
	WHEN	10	THEN	'All'
	WHEN	11	THEN	'All - excluding DFCI'
END	as	InstitutionSelected	
FROM #TempThirdPartyBilling
WHERE  (@RespInstitutionID = 10 OR Institutionid = @RespInstitutionID)
ORDER BY RespInstitution,Department,Unit,ProposalNo

END
GO



